<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>Mysql基础命令01 - 德国粗茶淡饭</title><meta name="Description" content="Mysql基础命令01"><meta property="og:title" content="Mysql基础命令01" />
<meta property="og:description" content="Mysql基础命令01" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://www.ctq6.cn/technology/mysql/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A401/" />
<meta property="og:image" content="https://www.ctq6.cn/logo.png"/>
<meta property="article:published_time" content="2021-02-16T19:38:19+08:00" />
<meta property="article:modified_time" content="2021-02-16T19:38:19+08:00" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://www.ctq6.cn/logo.png"/>

<meta name="twitter:title" content="Mysql基础命令01"/>
<meta name="twitter:description" content="Mysql基础命令01"/>
<meta name="application-name" content="LoveIt">
<meta name="apple-mobile-web-app-title" content="LoveIt"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="https://www.ctq6.cn/technology/mysql/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A401/" /><link rel="prev" href="https://www.ctq6.cn/technology/mysql/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E5%AE%89%E8%A3%85/" /><link rel="next" href="https://www.ctq6.cn/technology/mysql/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A402/" /><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/normalize.css@8.0.1/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13.0/css/all.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/animate.css@3.7.2/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "Mysql基础命令01",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/www.ctq6.cn\/technology\/mysql\/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A401\/"
        },"image": [{
                            "@type": "ImageObject",
                            "url": "https:\/\/www.ctq6.cn\/images\/Apple-Devices-Preview.png",
                            "width":  3200 ,
                            "height":  2048 
                        }],"genre": "technology","keywords": "Mysql","wordcount":  2079 ,
        "url": "https:\/\/www.ctq6.cn\/technology\/mysql\/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A401\/","datePublished": "2021-02-16T19:38:19+08:00","dateModified": "2021-02-16T19:38:19+08:00","license": "This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.","publisher": {
            "@type": "Organization",
            "name": "xxxx","logo": {
                    "@type": "ImageObject",
                    "url": "https:\/\/www.ctq6.cn\/images\/avatar.png",
                    "width":  528 ,
                    "height":  560 
                }},"author": {
                "@type": "Person",
                "name": "MikelPan"
            },"description": "Mysql基础命令01"
    }
    </script></head>
    <body header-desktop="fixed" header-mobile="auto"><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('auto' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'auto' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 生活 </a><a class="menu-item" href="/technology/"> 技术 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="/categories/documentation/"> 文档 </a><a class="menu-item" href="/about/"> 关于 </a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i>  </a><span class="menu-item delimiter"></span><a href="javascript:void(0);" class="menu-item language" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                        <select class="language-select" id="language-select-desktop" onchange="location = this.value;"><option value="/technology/mysql/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A401/" selected>简体中文</option></select>
                    </a><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-desktop">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-desktop" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-desktop" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-mobile">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-mobile" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-mobile" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="javascript:void(0);" class="search-cancel" id="search-cancel-mobile">
                        取消
                    </a>
                </div><a class="menu-item" href="/posts/" title="">生活</a><a class="menu-item" href="/technology/" title="">技术</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="/categories/documentation/" title="">文档</a><a class="menu-item" href="/about/" title="">关于</a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i></a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw"></i>
            </a><a href="javascript:void(0);" class="menu-item" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                    <select class="language-select" onchange="location = this.value;"><option value="/technology/mysql/mysql%E5%9F%BA%E7%A1%80%E5%91%BD%E4%BB%A401/" selected>简体中文</option></select>
                </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="page single special"><h1 class="single-title animated pulse faster">Mysql基础命令01</h1><div class="content" id="content"><h2 id="一mysql简介">一.Mysql简介</h2>
<p>MySQL是一个<a href="https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86%E7%B3%BB%E7%BB%9F" target="_blank" rel="noopener noreffer"><strong>关系型数据库管理系统</strong></a>，由瑞典MySQL AB 公司开发，目前属于 <a href="https://baike.baidu.com/item/Oracle" target="_blank" rel="noopener noreffer">Oracle</a> 旗下产品。MySQL 是最流行的<a href="https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86%E7%B3%BB%E7%BB%9F" target="_blank" rel="noopener noreffer">关系型数据库管理系统</a>之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。</p>
<h2 id="二mysql基本命令">二.Mysql基本命令</h2>
<h3 id="i库">I.库</h3>
<h3 id="1-创建数据库">1. 创建数据库</h3>
<blockquote>
<p>语法 ：create database 数据库名</p>
<p><strong>创建数据库ab</strong></p>
<p>create database ab；</p>
</blockquote>
<h3 id="2-查看数据库">2. 查看数据库</h3>
<blockquote>
<p><strong>显示所有的数据库</strong></p>
<p>show databases；</p>
<p><strong>以行显示所有数据库</strong></p>
<p>show databases \G</p>
</blockquote>
<h3 id="3删除数据库">3.删除数据库</h3>
<blockquote>
<p>语法:drop database 数据库名</p>
<p>删除数据库ab</p>
<p>drop database ab；</p>
</blockquote>
<h3 id="ii表">II.表</h3>
<h3 id="1-创建表">1. 创建表</h3>
<blockquote>
<p>语法:create table 表名 （字段名，类型，字段名，类型，字段名，类型）;</p>
<p>create table book（idint（10），namechar（40），age int）;</p>
</blockquote>
<h3 id="2查看表结构">2.查看表结构</h3>
<blockquote>
<p>desclist；</p>
<p>explain food.list;</p>
<p>show columns from food .list;</p>
<p>show columns from food. list like'%id';</p>
<p><strong>#查看表的创建过程，指定存储引擎，字符集</strong></p>
<p>show create table list；</p>
</blockquote>
<h3 id="3mysql存储引擎">3.mysql存储引擎</h3>
<p>mysql的存储引擎包括：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED</p>
<h3 id="4-删除表">4. 删除表</h3>
<blockquote>
<p>语法：drop table 表名</p>
<p>drop table list；</p>
</blockquote>
<h3 id="5修改表名">5.修改表名</h3>
<blockquote>
<p>语法：alter table 表名 rename 新表名；</p>
<p>alter table list rename lists；</p>
</blockquote>
<h3 id="6-修改表中的字段类型">6. 修改表中的字段类型</h3>
<blockquote>
<p>语法：alter table 表名 modify 要修改的字段名 字段名的新字段类型</p>
<p>alter table lists modifyid char（40）；</p>
</blockquote>
<h3 id="7修改表中字段名称和类型">7.修改表中字段名称和类型</h3>
<blockquote>
<p>语法：altertable 表名 change 原字段名 新字段名 新字段类型</p>
<p>alter table lists change id ids int（40）；</p>
</blockquote>
<h3 id="8表中添加字段">8.表中添加字段</h3>
<h4 id="1表中添加字段">1.表中添加字段</h4>
<blockquote>
<p>语法：alter table 表名 add 字段名 字段类型</p>
<p>alter table lists add sum int（50）；</p>
</blockquote>
<h4 id="2表第一行添加字段">2.表第一行添加字段</h4>
<blockquote>
<p>语法：alter table 表名 add 字段名 字段类型 first</p>
<p><strong>第一行添加字段</strong></p>
<p>alter table lists add sum int（50）first；</p>
</blockquote>
<h4 id="3在字段后添加字段">3.在字段后添加字段</h4>
<blockquote>
<p>语法：alter table 表名 add 字段名 字段类型 after su</p>
<p><strong>字段su后添加字段</strong>
alter table lists add so char（30）after su;</p>
</blockquote>
<h3 id="9删除表中字段">9.删除表中字段</h3>
<blockquote>
<p>语法：alter table 表名 drop 字段名</p>
<p>alter table lists drop so；</p>
</blockquote>
<h3 id="iii记录">III.记录</h3>
<h4 id="1字段中插入记录">1.字段中插入记录</h4>
<blockquote>
<p>语法：insert into 表名 values（1,&lsquo;zhangshan&rsquo;,2）;</p>
<p><strong>后面记录指定为空</strong></p>
<p>insert into lists values（1，2，‘shanshi’，null，null）；</p>
<p><strong>插入多条记录中间用分号隔开</strong></p>
<p>insert into lists valus （1，2，‘lisi’，null，null），（2，3，‘siji’，1，1）；</p>
<p><strong>指定字段插入</strong></p>
<p>insert into lists （su，ids）values（1，1）；</p>
</blockquote>
<h4 id="2查询表中记录">2.查询表中记录</h4>
<blockquote>
<p>语法：select * from 表名</p>
<p><strong>表示所有记录</strong></p>
<p>select * from lists；</p>
<p><strong>查询ids中记录</strong></p>
<p>select ids from lists；</p>
<p><strong>查询ids，su中记录</strong></p>
<p>select ids，su from lists；</p>
<p><strong>查看指定数据库中表内容</strong></p>
<p>select * from food.lists;    `</p>
</blockquote>
<h4 id="3删除表中记录">3.删除表中记录</h4>
<blockquote>
<p>语法：delete from表名 where 字段名=xx</p>
<p>delete from lists where ids=2；</p>
<p><strong>删除字段name记录为空的行</strong></p>
<p>delete from lists where name is null；</p>
</blockquote>
<h4 id="4更新记录">4.更新记录</h4>
<blockquote>
<p>语法：update 表名 set 字段名1=xx where 字段名2=xx</p>
<p>update lists set ids=1 where name=null；</p>
<p><strong>所有都变成2</strong></p>
<p>update lists set ids=2</p>
<p><strong>同时更新多个字段用分号隔开</strong></p>
<p>update lists set ids=3，name=‘lisi’ where su=1；</p>
</blockquote>
<h2 id="三sql基本语句查询">三.SQL基本语句查询</h2>
<h3 id="1-多字段查询">1. 多字段查询</h3>
<blockquote>
<p>语法：select 字段1，字段2 from 表名</p>
<p>select ids，name from lists；</p>
</blockquote>
<h3 id="2-去重复查询">2. 去重复查询</h3>
<blockquote>
<p>语法：select distinct 字段1，字段2 from 表名</p>
<p>select distinct ids，name from lists；</p>
</blockquote>
<h3 id="3使用and和or多条件查询">3.使用and和or多条件查询</h3>
<blockquote>
<p>语法：select  字段1，字段2 from 表名 where 字段1&gt;3 and 字段2&lt;5</p>
<p>select ids,name from lists where ids&gt;3 and name &lt;5;</p>
<p>select ids,name from lists where ids&gt;3 or name &lt;5;</p>
<p><strong>and与or同时存在时，先算and左右两边的，逻辑与先执行</strong></p>
<p>select * from lists where ids=3 and(su=1 or name =5);</p>
</blockquote>
<h3 id="4mysql区分大小写查询">4.mysql区分大小写查询</h3>
<blockquote>
<p>语法：select * from 表名 where binary 字段1=‘xxx’</p>
<p><strong>binary区分大小写</strong></p>
<p>select *from lists where binary name=‘LK’</p>
</blockquote>
<h3 id="5排序查询">5.排序查询</h3>
<blockquote>
<p>语法：select distinct 字段1，字段2 from 表名 orderby 字段名</p>
<p><strong>默认是升序排列</strong></p>
<p>select distinct ids，su from lists orderby ids；</p>
<p><strong>降序排列</strong></p>
<p>select distinct ids，su from lists orderby ids desc；</p>
</blockquote>
<h3 id="6查询引用别名">6.查询引用别名</h3>
<blockquote>
<p>语法：select * from 旧表名 新表名</p>
<p>select * from lists s；</p>
<p>语法：select 旧字段名 as 新字段名 from 表名</p>
<p><strong>指定字段别名</strong></p>
<p>select ids as s from lists；</p>
</blockquote>
<h3 id="7like查询"><strong>7.like查询</strong></h3>
<blockquote>
<p>语法：select 字段名1 字段名2 &hellip; from 表名 where 字段名1 like &lsquo;%abc&rsquo; or 字段名2 like &lsquo;%ABC&rsquo;</p>
<p>select abc ABC from abc1 where abc like &lsquo;%abc&rsquo; or ABC like &lsquo;%ABC&rsquo;</p>
</blockquote>
<h2 id="四常用select查询">四.常用select查询</h2>
<blockquote>
<p><strong>打印当前的日期和时间</strong></p>
<p>selectnow（）；</p>
<p><strong>打印当前的日期</strong></p>
<p>selectcurdate（）；</p>
<p><strong>打印当前的时间</strong></p>
<p>selectcurtime（）</p>
<p><strong>打印当前数据库</strong></p>
<p>selectdatabase（）；</p>
<p><strong>打印数据库版本</strong></p>
<p>selectversion（）；</p>
<p><strong>打印当前用户</strong></p>
<p>selectuser（）；</p>
</blockquote>
<h2 id="五导入导出数据库">五.导入导出数据库</h2>
<h3 id="1导入数据库">1.导入数据库</h3>
<h4 id="方法一">方法一</h4>
<blockquote>
<p>创建数据库  ：mysql -e ‘create database book’ -uroot -p123456</p>
<p>导入数据库  ：mysql -uroot -p123456 book</p>
</blockquote>
<h4 id="方法二">方法二</h4>
<blockquote>
<p>创建数据库  ：mysql  -e ‘create database book’ -uroot -p123456</p>
<p>导入数据库  ：source /root/book.sql  ** // 数据库所在路径**</p>
</blockquote>
<h3 id="2导出数据库">2.导出数据库</h3>
<blockquote>
<p>mysqldump -uroot -p123456 数据库名&gt;数据库文件名</p>
<p>mysqldump -uroot -p123456 book&gt;book.sql</p>
<p><strong>导出包含建库语句</strong></p>
<p>mysqldump -uroot -p123456 -B book&gt;book.sql</p>
<p><strong>导出所有数据库</strong></p>
<p>mysqldump -uroot -p123456 -A book&gt;book.sql</p>
<p><strong>导出数据库到文件</strong></p>
<p>select * from lists outfile ‘/tmp/123.txt' ;</p>
</blockquote>
</div><div id="comments"></div></div></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2019 - 2021</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank">mikel pan</a></span>&nbsp;|&nbsp;<span class="license"><a rel="license external nofollow noopener noreffer" href="https://creativecommons.org/licenses/by-nc/4.0/" target="_blank">CC BY-NC 4.0</a></span><span class="icp-splitter">&nbsp;|&nbsp;</span><br class="icp-br"/>
                    <span class="icp"><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备2021047442号</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/css/lightgallery.min.css"><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/smooth-scroll@16.1.3/dist/smooth-scroll.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/autocomplete.js@0.37.1/dist/autocomplete.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/algoliasearch@4.2.0/dist/algoliasearch-lite.umd.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lazysizes@5.2.2/lazysizes.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/js/lightgallery.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-thumbnail.js@1.2.0/dist/lg-thumbnail.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-zoom.js@1.2.0/dist/lg-zoom.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/sharer.js@0.4.0/sharer.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":10},"comment":{},"lightGallery":{"actualSize":false,"exThumbImage":"data-thumbnail","hideBarsDelay":2000,"selector":".lightgallery","speed":400,"thumbContHeight":80,"thumbWidth":80,"thumbnail":true},"search":{"algoliaAppID":"REQJX89W85","algoliaIndex":"index.zh-cn","algoliaSearchKey":"63fa048de9b35627f46672e95abc14df","highlightTag":"em","maxResultLength":10,"noResultsFound":"没有找到结果","snippetLength":50,"type":"algolia"}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
